﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using IHISCETeim.BLL;

using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using NPOI.SS.UserModel;
using NPOI.SS.Util;

namespace IHISCETeim.Web.Export.FXModel
{
    public partial class ReportYQ2QKFMX : ExportPageBase
    {
        protected override string xlsTemplateFileName
        {
            get
            {
                return "逾期3期以上客户明细报表.xls";
            }
        }
        protected override string SheetName
        {
            get
            {
                return "汇总明细";
            }
        }
        protected string LeaseCompany
        {
            get
            {
                return GetRequestValue<string>("LeaseCompany", "");
            }
        }

        protected void Page_Load(object sender, EventArgs e)
        {
            InitializeWorkbook();



            //各经销商汇总
            WriteSheet0();


            // hssfworkbook.SetSheetName(0, temptitle);
            DataTable aNList = FXFinanceLeaseController.ReportGetAliasNameJXSYQ2CSLSBP("");
            string temptitle = "";
            //各个经销商
            string AliasName = "";
            int sheetindex = 2;//从第二个工作表开始
            foreach (DataRow dr in aNList.Rows)
            {
                AliasName = dr["AliasName"].ToString();
                DataTable DT = FXFinanceLeaseController.ReportYQ2QKFMX(AliasName)[0];
                ISheet sheet2 = hssfworkbook.CloneSheet(1);
                hssfworkbook.SetSheetName(sheetindex, AliasName);
                //标题
                temptitle = string.Format("经销商{0}逾期3期及以上客户明细", AliasName);

                WriteSheet(sheet2, DT, temptitle);
                sheet2.ForceFormulaRecalculation = true;
                sheetindex++;
            }

            //明细表汇总
            DataTable allDT = FXFinanceLeaseController.ReportYQ2QKFMX("")[0];
            temptitle = "经销商逾期3期及以上客户明细";

            ISheet sheet0 = hssfworkbook.GetSheetAt(1);
            WriteSheet(sheet0, allDT, temptitle);

            WriteToFile();

        }

        private void WriteSheet(ISheet sheet1, DataTable exportDT, string title)
        {
            sheet1.GetRow(0).GetCell(0).SetCellValue(title);
            
            //sheet1.GetRow(1).GetCell(7).SetCellValue(IHISCETeim.BLL.FXFinanceLeaseController.GetLastRefresDate());
            int rowindexBase = 2;
            int rowindex = 0;
            int rowReal = rowindex + rowindexBase;
            if (exportDT.Rows.Count <= 0)
                return;
            foreach (DataRow dr in exportDT.Rows)
            {
                HSSFRow row1 = (HSSFRow)sheet1.CreateRow(rowReal);
                // row1.CreateCell(0).SetCellValue(1);
                row1.CreateCell(0).SetCellValue(rowindex+1);
                row1.CreateCell(1).SetCellValue(dr["经销商"].ToString());
                row1.CreateCell(2).SetCellValue(dr["用户名"].ToString());
                row1.CreateCell(3).SetCellValue(dr["合同号"].ToString());
                row1.CreateCell(4).SetCellValue(dr["机号"].ToString());
                row1.CreateCell(5).SetCellValue(dr["月租金"].ToString());
                row1.CreateCell(6).SetCellValue(Convert.ToDouble(dr["总期数"]));                
                row1.CreateCell(7).SetCellValue(Convert.ToDouble(dr["已付期数"]));
                row1.CreateCell(8).SetCellValue(Convert.ToDouble(dr["剩余期数"]));
                row1.CreateCell(9).SetCellValue(Convert.ToDouble(dr["逾期期数"]));
                row1.CreateCell(10).SetCellValue(Convert.ToDouble(dr["逾期金额"]));
                row1.CreateCell(11).SetCellValue("");
                row1.CreateCell(12).SetCellValue(Convert.ToDouble(dr["降到3_9期"].ToString()));
                row1.CreateCell(13).SetCellValue(Convert.ToDouble(dr["降到2_9期"].ToString()));
                rowindex++;
                rowReal = rowindex + rowindexBase;
            }

            //合计行
            HSSFRow rowSum = (HSSFRow)sheet1.CreateRow(rowReal);
            rowSum.CreateCell(0).SetCellValue("合计:");
            //rowSum.CreateCell(1).SetCellFormula("sum(B" + (rowindexBase + 1).ToString() + ":B" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(2).SetCellFormula("sum(C" + (rowindexBase + 1).ToString() + ":C" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(3).SetCellFormula("sum(D" + (rowindexBase + 1).ToString() + ":D" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(4).SetCellFormula("sum(E" + (rowindexBase + 1).ToString() + ":E" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(5).SetCellFormula("sum(F" + (rowindexBase + 1).ToString() + ":F" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(6).SetCellFormula("sum(G" + (rowindexBase + 1).ToString() + ":G" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(7).SetCellFormula("sum(H" + (rowindexBase + 1).ToString() + ":H" + (rowReal).ToString() + ")");

            //rowSum.CreateCell(8).SetCellFormula("sum(I" + (rowindexBase + 1).ToString() + ":I" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(9).SetCellFormula("sum(J" + (rowindexBase + 1).ToString() + ":J" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(10).SetCellFormula("sum(K" + (rowindexBase + 1).ToString() + ":K" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(11).SetCellFormula("sum(L" + (rowindexBase + 1).ToString() + ":L" + (rowReal).ToString() + ")");
            rowSum.CreateCell(12).SetCellFormula("sum(M" + (rowindexBase + 1).ToString() + ":M" + (rowReal).ToString() + ")");
            rowSum.CreateCell(13).SetCellFormula("sum(N" + (rowindexBase + 1).ToString() + ":N" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(14).SetCellFormula("sum(O" + (rowindexBase + 1).ToString() + ":O" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(15).SetCellFormula("sum(P" + (rowindexBase + 1).ToString() + ":P" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(16).SetCellFormula("sum(Q" + (rowindexBase + 1).ToString() + ":Q" + (rowReal).ToString() + ")");

            sheet1.ForceFormulaRecalculation = true;
        }

        private void WriteSheet0()
        {
            DataTable dtc1 = FXFinanceLeaseController.ReportYQ2QKFMXTotal();

            ISheet sheet1 = hssfworkbook.GetSheetAt(0);
            int rowindexBase = 1;
            int rowindex = 0;
            int rowReal = rowindex + rowindexBase;
            foreach (DataRow dr in dtc1.Rows)
            {
                HSSFRow row1 = (HSSFRow)sheet1.CreateRow(rowReal);
                // row1.CreateCell(0).SetCellValue(1);
                row1.CreateCell(0).SetCellValue(dr["经销商"].ToString());
                row1.CreateCell(1).SetCellValue(Convert.ToInt32(dr["总单数"].ToString()));
                row1.CreateCell(2).SetCellValue(Convert.ToDouble(dr["结清单数"].ToString()));
                row1.CreateCell(3).SetCellValue(Convert.ToInt32(dr["剩余单数"].ToString()));
                row1.CreateCell(4).SetCellValue(Convert.ToDouble(dr["未结清用户月租金"].ToString()));
                row1.CreateCell(5).SetCellValue(Convert.ToDouble(dr["剩余总租金"].ToString()));
                row1.CreateCell(6).SetCellValue(Convert.ToDouble(dr["逾期1期"].ToString()));
                row1.CreateCell(7).SetCellValue(Convert.ToDouble(dr["逾期2期"].ToString()));
                //---
                row1.CreateCell(8).SetCellValue(Convert.ToDouble(dr["逾期3期"].ToString()));
                row1.CreateCell(9).SetCellValue(Convert.ToDouble(dr["逾期4期"].ToString()));
                row1.CreateCell(10).SetCellValue(Convert.ToDouble(dr["逾期5期"].ToString()));
                row1.CreateCell(11).SetCellValue(Convert.ToDouble(dr["逾期6期"].ToString()));
                row1.CreateCell(12).SetCellValue(Convert.ToDouble(dr["逾期7期"].ToString()));
                row1.CreateCell(13).SetCellValue(Convert.ToDouble(dr["逾期8期"].ToString()));
                row1.CreateCell(14).SetCellValue(Convert.ToDouble(dr["逾期总金额"].ToString()));
                row1.CreateCell(15).SetCellValue(Convert.ToDouble(dr["降到3_9期"].ToString()));
                row1.CreateCell(16).SetCellValue(Convert.ToDouble(dr["降到2_9期"].ToString()));
                rowindex++;
                rowReal = rowindex + rowindexBase;
            }


            //合计行
            HSSFRow rowSum = (HSSFRow)sheet1.CreateRow(rowReal);
            rowSum.CreateCell(0).SetCellValue("合计:");
            //rowSum.CreateCell(1).SetCellFormula("sum(B" + (rowindexBase + 1).ToString() + ":B" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(2).SetCellFormula("sum(C" + (rowindexBase + 1).ToString() + ":C" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(3).SetCellFormula("sum(D" + (rowindexBase + 1).ToString() + ":D" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(4).SetCellFormula("sum(E" + (rowindexBase + 1).ToString() + ":E" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(5).SetCellFormula("sum(F" + (rowindexBase + 1).ToString() + ":F" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(6).SetCellFormula("sum(G" + (rowindexBase + 1).ToString() + ":G" + (rowReal).ToString() + ")");
            //rowSum.CreateCell(7).SetCellFormula("sum(H" + (rowindexBase + 1).ToString() + ":H" + (rowReal).ToString() + ")");

            rowSum.CreateCell(8).SetCellFormula("sum(I" + (rowindexBase + 1).ToString() + ":I" + (rowReal).ToString() + ")");
            rowSum.CreateCell(9).SetCellFormula("sum(J" + (rowindexBase + 1).ToString() + ":J" + (rowReal).ToString() + ")");
            rowSum.CreateCell(10).SetCellFormula("sum(K" + (rowindexBase + 1).ToString() + ":K" + (rowReal).ToString() + ")");
            rowSum.CreateCell(11).SetCellFormula("sum(L" + (rowindexBase + 1).ToString() + ":L" + (rowReal).ToString() + ")");
            rowSum.CreateCell(12).SetCellFormula("sum(M" + (rowindexBase + 1).ToString() + ":M" + (rowReal).ToString() + ")");
            rowSum.CreateCell(13).SetCellFormula("sum(N" + (rowindexBase + 1).ToString() + ":N" + (rowReal).ToString() + ")");
            rowSum.CreateCell(14).SetCellFormula("sum(O" + (rowindexBase + 1).ToString() + ":O" + (rowReal).ToString() + ")");
            rowSum.CreateCell(15).SetCellFormula("sum(P" + (rowindexBase + 1).ToString() + ":P" + (rowReal).ToString() + ")");
            rowSum.CreateCell(16).SetCellFormula("sum(Q" + (rowindexBase + 1).ToString() + ":Q" + (rowReal).ToString() + ")");

            sheet1.ForceFormulaRecalculation = true;
        }
    }
}